/*

This is the script which calls the sp_individual_raise
procedure which. The script searches for the two lowest paid 
SDI employees, and give the lowest an 8% raise and the second 
lowest a 5% raise.

pre-cond: the DB to exist and the Stored procedure as well 
post-cond: the salary is raised by the indicated percentage

Name: Emanuel Reyes
Date: 04/25/10

*/

CREATE PROCEDURE raiseSalaryLowestEmp 
	AS
	BEGIN

		DECLARE @lowestssn char(9)
		DECLARE @seclowestssn char(9)

		SELECT @lowestssn = (SELECT emp_ssn
				  FROM employee 
				  WHERE post_merge_salary = (SELECT MIN(post_merge_salary)
									  FROM employee 
									  WHERE company = 'SDI')
				  AND company = 'SDI')

		SELECT @seclowestssn = (SELECT emp_ssn
					 FROM employee
					 WHERE post_merge_salary = (SELECT MIN(post_merge_salary)
										 FROM employee
										 WHERE emp_ssn != @lowestssn
										 AND company = 'SDI')
					 AND company = 'SDI');

		EXEC sp_individual_raise @lowestssn, 5.0
		EXEC sp_individual_raise @seclowestssn, 8.0

	END
